/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dal;

import dto.BaiThiDTO;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Stanley
 */
public class StaticticsDAO {

    public ResultSet getBaiThi(String userType, String maTS, int start, int pageSize) {
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = createQueryStringForGetBaiThi(userType, maTS, start, pageSize);
            return query.executeQuery(sql);
        } catch (Exception e) {
            return null;
        }
    }

    public int countBaiThi(String userType, String maTS) {
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = createQueryStringForCount(userType, maTS);
            ResultSet rs = query.executeQuery(sql);
            rs.next();
            return rs.getInt("numrow");
        } catch (Exception e) {
            return 0;
        }
    }

    private String createQueryStringForGetBaiThi(String userType, String maTS, int start, int pageSize) {
        String limit = " limit " + Integer.toString(start) + " , " + Integer.toString(pageSize);

        String condition = null;
        String order = " order by thoigianbd desc ";
        if (userType.equals("1")) // quản trị
        {
            condition = " 1=1 ";
        } else // thí sinh
        {
            condition = " thisinh = " + maTS;
        }
        String table = " baithi left join thisinh on thisinh = maTS ";
        String sql = "select * from " + table + " where " + condition + order + limit;
        return sql;
    }

    private String createQueryStringForCount(String userType, String maTS) {
        String condition = null;
        if (userType.equals("1")) // quản trị
        {
            condition = " 1=1 ";
        } else // thí sinh
        {
            condition = " thisinh = " + maTS;
        }
        String table = " baithi left join thisinh on thisinh = maTS ";
        String sql = "select count(*) as numrow from " + table + " where " + condition;
        return sql;
    }
    
    public ArrayList getBaiThi(String maTS, String dokho){
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement)connection.createStatement();
            String sql = "SELECT * FROM baithi as bt left join dethi as dt on bt.dethi = dt.maDT where dt.loaidethi = " + 
                    dokho + " and bt.thisinh = " + maTS + " order by bt.thoigianbd desc limit 10";
            ResultSet rs =  query.executeQuery(sql);
            ArrayList dsBaiThi = new ArrayList();
            while (rs.next()) {                
                BaiThiDTO info = new BaiThiDTO(
                        rs.getInt("dethi"), 
                        rs.getInt("thisinh"), 
                        rs.getTimestamp("thoigianbd"), 
                        rs.getTimestamp("thoigiankt"), 
                        rs.getFloat("diem"));
                dsBaiThi.add(info);
            }
            ArrayList result = new ArrayList();
            for(int i = dsBaiThi.size() - 1; i >= 0; i--){
                result.add(dsBaiThi.get(i));
            }
            return result.isEmpty() ? null : result;
        } catch (Exception e) {
            return null;
        }
    }
}
